package com.hethongviet.vending.helper.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.vending.Utils;
import com.hethongviet.vending.model.ProductSell;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ProductSellSQLite
{
	public static final String TABLE_NAME = "product_sell";

	public static final String COLUMN_ID_DB = "idDB";

	public static final String COLUMN_ID_BOX = "id_box";
	public static final String COLUMN_CODE_PRODUCT = "code_product";
	public static final String COLUMN_ACCOUNT_NUMBER = "account";

	public static final String COLUMN_PRICE = "price";
	public static final String COLUMN_IS_DELIVERIED = "is_deliveried";
	public static final String COLUMN_IS_SENT_SERVER_OK = "sent_server";
	public static final String COLUMN_DATE = "date";

	public static final String COLUMN_MACHINE_CODE = "machine_code";
	public static final String COLUMN_WORKER_NUMBER_1 = "worker_number_1";
	public static final String COLUMN_WORKER_NUMBER_2 = "work_number_2";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;

	private String[] _allColumns = { COLUMN_ID_DB, COLUMN_ACCOUNT_NUMBER,
			COLUMN_ID_BOX, COLUMN_CODE_PRODUCT, COLUMN_DATE,
			COLUMN_IS_DELIVERIED, COLUMN_IS_SENT_SERVER_OK, COLUMN_PRICE,
			COLUMN_MACHINE_CODE, COLUMN_WORKER_NUMBER_1, COLUMN_WORKER_NUMBER_2 };

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "
			+ COLUMN_ACCOUNT_NUMBER + " text, " + COLUMN_ID_BOX + " integer, "
			+ COLUMN_CODE_PRODUCT + " text, " + COLUMN_DATE + " text, "
			+ COLUMN_IS_DELIVERIED + " integer, " + COLUMN_IS_SENT_SERVER_OK
			+ " integer, " + COLUMN_PRICE + " integer, " + COLUMN_MACHINE_CODE
			+ " text, " + COLUMN_WORKER_NUMBER_1 + " text, "
			+ COLUMN_WORKER_NUMBER_2 + " text";

	public ProductSellSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(ProductSell productSell, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == productSell.getIdDB())
				return true;
		}
		return false;
	}

	/**
	 * @param productSell
	 * @return row id -1: insert fail
	 */
	public long insert(ProductSell productSell)
	{
		open();

		Utils.debug("insert -- " + productSell.getIdBox());
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + productSell.getIdDB(),
				null);

		long temp = 0;
		if (!dataExist(productSell, cursor))
		{
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery(
					"INSERT INTO " + TABLE_NAME + "(" + COLUMN_ACCOUNT_NUMBER
							+ ", " + COLUMN_ID_BOX + ", " + COLUMN_CODE_PRODUCT
							+ ", " + COLUMN_DATE + ", " + COLUMN_IS_DELIVERIED
							+ ", " + COLUMN_IS_SENT_SERVER_OK + ", "
							+ COLUMN_PRICE + ", " + COLUMN_MACHINE_CODE + ", "
							+ COLUMN_WORKER_NUMBER_1 + ", "
							+ COLUMN_WORKER_NUMBER_2
							+ ") values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

					new String[] { productSell.getAccountNumber(),
							productSell.getIdBox() + "",
							productSell.getCodeProduct(),
							productSell.getDate() + "",
							productSell.isDelivered() ? "1" : "0",
							productSell.isSentServerOk() ? "1" : "0",
							productSell.getPrice() + "",
							productSell.getMachineCode(),
							productSell.getWorkerNumber1(),
							productSell.getWorkerNumber2() });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast())
				temp = cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			Utils.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery(
					"UPDATE " + TABLE_NAME + " SET " + COLUMN_ACCOUNT_NUMBER
							+ " = ?, " + COLUMN_ID_BOX + " = ?, "
							+ COLUMN_CODE_PRODUCT + " = ?, " + COLUMN_DATE
							+ " = ?, " + COLUMN_IS_DELIVERIED + " = ?, "
							+ COLUMN_IS_SENT_SERVER_OK + " = ?, "
							+ COLUMN_PRICE + " = ?, " + COLUMN_MACHINE_CODE
							+ " = ?, " + COLUMN_WORKER_NUMBER_1 + " = ?, "
							+ COLUMN_WORKER_NUMBER_2 + " = ? " + "WHERE "
							+ COLUMN_ID_DB + " = " + productSell.getIdDB(),
					new String[] { productSell.getAccountNumber(),
							productSell.getIdBox() + "",
							productSell.getCodeProduct(),
							productSell.getDate() + "",
							productSell.isDelivered() ? "1" : "0",
							productSell.isSentServerOk() ? "1" : "0",
							productSell.getPrice() + "",
							productSell.getMachineCode(),
							productSell.getWorkerNumber1(),
							productSell.getWorkerNumber2() });
			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast())
				temp = cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return temp;
	}

	public void deleteAll()
	{
		open();

		System.out.println("project deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public void updateIdDB(ProductSell productSell)
	{
		open();

		Utils.debug("1111111111111 update product id DB = "
				+ productSell.getCodeProduct());
		/* Update to database */
		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			ProductSell productSellGet = cursorToProductSell(cursor);
			if (productSellGet.getDate() == productSell.getDate())
			{
				productSell.setIdDB(productSellGet.getIdDB());
				break;
			}
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
	}

	public List<ProductSell> getAll()
	{
		open();

		List<ProductSell> listProductSells = new ArrayList<ProductSell>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			ProductSell productSell = cursorToProductSell(cursor);
			listProductSells.add(productSell);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listProductSells;
	}

	private ProductSell cursorToProductSell(Cursor cursor)
	{
		ProductSell productSell = new ProductSell();
		productSell.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		productSell.setAccountNumber(cursor.getString(cursor.getColumnIndex(COLUMN_ACCOUNT_NUMBER)));
		productSell.setIdBox(cursor.getInt(cursor.getColumnIndex(COLUMN_ID_BOX)));
		productSell.setCodeProduct(cursor.getString(cursor.getColumnIndex(COLUMN_CODE_PRODUCT)));

		String dateString = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
		long date = -1;
		try
		{
			date = Long.parseLong(dateString);
		} catch (NumberFormatException e)
		{
			e.printStackTrace();
		}
		productSell.setDate(date);

		productSell.setDelivered(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_DELIVERIED)) == 1);
		productSell.setSentServerOk(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_SENT_SERVER_OK)) == 1);
		productSell.setPrice(cursor.getInt(cursor.getColumnIndex(COLUMN_PRICE)));

		productSell.setMachineCode(cursor.getString(cursor.getColumnIndex(COLUMN_MACHINE_CODE)));
		productSell.setWorkerNumber1(cursor.getString(cursor.getColumnIndex(COLUMN_WORKER_NUMBER_1)));
		productSell.setWorkerNumber2(cursor.getString(cursor.getColumnIndex(COLUMN_WORKER_NUMBER_2)));

		return productSell;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		isExist = cursor != null && cursor.getCount() > 0;

		cursor.close();
		close();

		return isExist;
	}
}
